﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Reflection;
using System.IO;
using System.Runtime.InteropServices;

namespace Clipper.DAL
{
    public class ExcelHelper
    {
        [DllImport("User32.dll", CharSet = CharSet.Auto)]
        public static extern int GetWindowThreadProcessId(IntPtr hwnd, out int ID);   
        #region 将数据保存到excel
        /// <summary>
        /// 将dataTable导出到新建excel
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="fileName"></param>
        public static void DataTableToExcel(DataTable dt, string fileName,string openPwd,string writePwd)
        {
            //System.Diagnostics.Process[] arrProcesses;
            //arrProcesses = System.Diagnostics.Process.GetProcessesByName("Excel");
            //foreach (System.Diagnostics.Process myProcess in arrProcesses)
            //{
            //    myProcess.Kill();
            //}

            Object missing = Missing.Value;
            Microsoft.Office.Interop.Excel.Application m_objExcel =
             new Microsoft.Office.Interop.Excel.Application();
            try
            {
                Microsoft.Office.Interop.Excel.Workbooks m_objWorkBooks = m_objExcel.Workbooks;
                Microsoft.Office.Interop.Excel.Workbook m_objWorkBook = m_objWorkBooks.Add(true);
                Microsoft.Office.Interop.Excel.Sheets m_objWorkSheets = m_objWorkBook.Sheets; ;
                Microsoft.Office.Interop.Excel.Worksheet m_objWorkSheet =
                 (Microsoft.Office.Interop.Excel.Worksheet)m_objWorkSheets[1];
                m_objWorkSheet.Name = dt.TableName;
                int intFeildCount = dt.Columns.Count;
                for (int col = 0; col < intFeildCount; col++)
                {
                    m_objWorkSheet.Cells[1, col + 1] = dt.Columns[col].ToString();
                }
                for (int intRowCount = 0; intRowCount < dt.Rows.Count; intRowCount++)
                {
                    for (int intCol = 0; intCol < dt.Columns.Count; intCol++)
                    {
                        m_objWorkSheet.Cells[intRowCount + 2, intCol + 1] = "'" + dt.Rows[intRowCount][intCol].ToString();
                    }
                }
                if (File.Exists(fileName))
                {
                    File.Delete(fileName);
                }
                m_objExcel.Visible = false;

                //保存
                m_objWorkBook.SaveAs(fileName, missing,openPwd,writePwd, missing,
                missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
                missing, missing, missing, missing, missing);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                //m_objExcel.Quit();
                //m_objExcel = null;
                IntPtr t = new IntPtr(m_objExcel.Hwnd);
                int k = 0;
                GetWindowThreadProcessId(t, out  k);
                System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k);
                p.Kill();  
            }
        }

        /// <summary>
        /// 将DataSet导出到新建excel
        /// </summary>
        /// <param name="ds"></param>
        /// <param name="fileName"></param>
        public static void DataSetToExcel(DataSet ds, string fileName, string openPwd, string writePwd)
        {
            //System.Diagnostics.Process[] arrProcesses;
            //arrProcesses = System.Diagnostics.Process.GetProcessesByName("Excel");
            //foreach (System.Diagnostics.Process myProcess in arrProcesses)
            //{
            //    myProcess.Kill();
            //}

            Object missing = Missing.Value;
            Microsoft.Office.Interop.Excel.Application m_objExcel =
             new Microsoft.Office.Interop.Excel.Application();
            try
            {
                Microsoft.Office.Interop.Excel.Workbooks m_objWorkBooks = m_objExcel.Workbooks;
                Microsoft.Office.Interop.Excel.Workbook m_objWorkBook = m_objWorkBooks.Add(true);
                Microsoft.Office.Interop.Excel.Sheets m_objWorkSheets = m_objWorkBook.Sheets;
                Microsoft.Office.Interop.Excel.Worksheet m_objWorkSheet;
                for (int i = 0; i < ds.Tables.Count; i++)
                {
                    if (i == 0)
                    {
                         m_objWorkSheet =
                         (Microsoft.Office.Interop.Excel.Worksheet)m_objWorkSheets[i + 1];
                    }
                    else
                    {
                        m_objWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)m_objWorkBook.Worksheets.Add(System.Reflection.Missing.Value,
                            System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value);
                    }
                    DataTable dt = ds.Tables[i];
                    m_objWorkSheet.Name = dt.TableName;
                    int intFeildCount = dt.Columns.Count;
                    for (int col = 0; col < intFeildCount; col++)
                    {
                        m_objWorkSheet.Cells[1, col + 1] = dt.Columns[col].ToString();
                    }
                    for (int intRowCount = 0; intRowCount < dt.Rows.Count; intRowCount++)
                    {
                        for (int intCol = 0; intCol < dt.Columns.Count; intCol++)
                        {
                            m_objWorkSheet.Cells[intRowCount + 2, intCol + 1] = "'" + dt.Rows[intRowCount][intCol].ToString();
                        }
                    }
                }
                if (File.Exists(fileName))
                {
                    File.Delete(fileName);
                }
                m_objExcel.Visible = false;

                //保存
                m_objWorkBook.SaveAs(fileName, missing, openPwd, writePwd, missing,
                missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
                missing, missing, missing, missing, missing);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                //m_objExcel.Quit();
                //m_objExcel = null;
                IntPtr t = new IntPtr(m_objExcel.Hwnd);
                int k = 0;
                GetWindowThreadProcessId(t, out  k);
                System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k);
                p.Kill();                   
            }
        }

        #endregion

        #region 从excel读取数据
        /// <summary>
        ///  将excel转化为dataTable
        /// </summary>
        /// <param name="excelFilePath">路径</param>
        /// <param name="openPwd">打开密码</param>
        /// <param name="writePwd">写密码</param>
        /// <returns>dataTable</returns>
        public static System.Data.DataTable DataTableFromExcel(string excelFilePath, string openPwd, string writePwd)
        {
            return DataTableFromExcel(excelFilePath, openPwd, writePwd, 1);
        }
        /// <summary>
        /// 将excel转化为dataTable
        /// </summary>
        /// <param name="excelFilePath">路径</param>
        /// <param name="openPwd">打开密码</param>
        /// <param name="writePwd">写密码</param>
        /// <param name="sheetIndex">打开的第几个sheet，第一个从1开始</param>
        /// <returns>dataTable</returns>
        public static System.Data.DataTable DataTableFromExcel(string excelFilePath, string openPwd, string writePwd,int sheetIndex)
        {
            Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
            Microsoft.Office.Interop.Excel.Sheets sheets;
            Microsoft.Office.Interop.Excel.Workbook workbook = null;
            object missiong = System.Reflection.Missing.Value;
            System.Data.DataTable dt = new System.Data.DataTable();
            try
            {
                if (app == null)
                {
                    return null;
                }
                workbook = app.Workbooks.Open(excelFilePath, missiong, missiong, missiong, openPwd, writePwd, missiong, missiong, missiong, missiong, missiong, missiong, missiong, missiong, missiong);
                //将数据读入到DataTable中——Start   
                sheets = workbook.Worksheets;
                Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)sheets.get_Item(sheetIndex);//读取第一张表
                if (worksheet == null)
                    return null;
                dt.TableName = worksheet.Name;
                string cellContent;
                int iRowCount = worksheet.UsedRange.Rows.Count;
                int iColCount = worksheet.UsedRange.Columns.Count;
                Microsoft.Office.Interop.Excel.Range range;
                //负责列头Start
                DataColumn dc;
                int ColumnID = 1;
                range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, 1];
                while (range.Text.ToString().Trim() != "")
                {
                    dc = new DataColumn();
                    dc.DataType = System.Type.GetType("System.String");
                    dc.ColumnName = range.Text.ToString().Trim();
                    dt.Columns.Add(dc);
                    range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, ++ColumnID];
                }
                //End
                for (int iRow = 2; iRow <= iRowCount; iRow++)
                {
                    DataRow dr = dt.NewRow();
                    for (int iCol = 1; iCol <= iColCount; iCol++)
                    {
                        range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[iRow, iCol];
                        cellContent = (range.Value2 == null) ? "" : range.Text.ToString();
                        //if (iRow == 1)
                        //{
                        //    dt.Columns.Add(cellContent);
                        //}
                        //else
                        //{
                        dr[iCol - 1] = cellContent;
                        //}
                    }
                    //if (iRow != 1)
                    dt.Rows.Add(dr);
                }
                //将数据读入到DataTable中——End
                return dt;
            }
            catch
            {
                return null;
            }
            finally
            {
                //workbook.Close(false, missiong, missiong);
                //System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
                //workbook = null;
                //app.Workbooks.Close();
                //app.Quit();
                //System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
                //app = null;
                //GC.Collect();
                //GC.WaitForPendingFinalizers();
                IntPtr t = new IntPtr(app.Hwnd);
                int k = 0;
                GetWindowThreadProcessId(t, out  k);
                System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k);
                p.Kill();  
            }

        }

        /// <summary>
        /// 将excel转化为DataSet
        /// </summary>
        /// <param name="excelFilePath">路径</param>
        /// <param name="openPwd">打开密码</param>
        /// <param name="writePwd">写密码</param>
        /// <returns>DataSet</returns>
        public static System.Data.DataSet DataSetFromExcel(string excelFilePath, string openPwd, string writePwd)
        {
            Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
            Microsoft.Office.Interop.Excel.Sheets sheets;
            Microsoft.Office.Interop.Excel.Workbook workbook = null;
            object missiong = System.Reflection.Missing.Value;
            System.Data.DataTable dt = null;
            System.Data.DataSet ds = new DataSet();
            try
            {
                if (app == null)
                {
                    return null;
                }
                app.Visible = false;
                workbook = app.Workbooks.Open(excelFilePath, missiong, missiong, missiong, openPwd, writePwd, missiong, missiong, missiong, missiong, missiong, missiong, missiong, missiong, missiong);
                
                //将数据读入到DataTable中——Start   
                sheets = workbook.Worksheets;
                for (int i = 1; i <= sheets.Count; i++)
                {

                    Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)sheets.get_Item(i);//读取每一张表
                    if (worksheet == null)
                        return null;
                    dt = new DataTable();
                    dt.TableName = worksheet.Name;
                    string cellContent;
                    int iRowCount = worksheet.UsedRange.Rows.Count;
                    int iColCount = worksheet.UsedRange.Columns.Count;
                    Microsoft.Office.Interop.Excel.Range range;
                    //负责列头Start
                    DataColumn dc;
                    int ColumnID = 1;
                    range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, 1];
                    while (range.Text.ToString().Trim() != "")
                    {
                        dc = new DataColumn();
                        dc.DataType = System.Type.GetType("System.String");
                        dc.ColumnName = range.Text.ToString().Trim();
                        dt.Columns.Add(dc);
                        range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, ++ColumnID];
                    }
                    //负责列头End

                    //将数据读入到DataTable中——Start
                    for (int iRow = 2; iRow <= iRowCount; iRow++)
                    {
                        DataRow dr = dt.NewRow();
                        for (int iCol = 1; iCol <= iColCount; iCol++)
                        {
                            range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[iRow, iCol];
                            cellContent = (range.Value2 == null) ? "" : range.Text.ToString();
                            //if (iRow == 1)
                            //{
                            //    dt.Columns.Add(cellContent);
                            //}
                            //else
                            //{
                            dr[iCol - 1] = cellContent;
                            //}
                        }
                        //if (iRow != 1)
                        dt.Rows.Add(dr);
                    }
                    //将数据读入到DataTable中——End
                    ds.Tables.Add(dt);

                }
                return ds;
            }
            catch
            {
                return null;
            }
            finally
            {
                IntPtr t = new IntPtr(app.Hwnd);
                int k = 0;
                GetWindowThreadProcessId(t, out  k);
                System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k);
                p.Kill();
            }

        }

        /// <summary>
        /// 检查数据模板的版本号
        /// </summary>
        /// <param name="excelFilePath">路径</param>
        /// <param name="openPwd">打开密码</param>
        /// <param name="writePwd">写密码</param>
        /// <returns>DataSet</returns>
        public static bool CheckFileVersion(string excelFilePath, string openPwd, string writePwd,string version)
        {
            bool res = false;
            Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
            Microsoft.Office.Interop.Excel.Sheets sheets;
            Microsoft.Office.Interop.Excel.Workbook workbook = null;
            object missiong = System.Reflection.Missing.Value;
            System.Data.DataTable dt = null;
            System.Data.DataSet ds = new DataSet();
            try
            {
                if (app == null)
                {
                    return false;
                }
                workbook = app.Workbooks.Open(excelFilePath, missiong, missiong, missiong, openPwd, writePwd, missiong, missiong, missiong, missiong, missiong, missiong, missiong, missiong, missiong);
                //将数据读入到DataTable中——Start   
                sheets = workbook.Worksheets;
                Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)sheets.get_Item(sheets.Count);//读取最后一张表
                if (worksheet == null)
                    return false;
                dt = new DataTable();
                dt.TableName = worksheet.Name;
                int iRowCount = worksheet.UsedRange.Rows.Count;
                int iColCount = worksheet.UsedRange.Columns.Count;
                Microsoft.Office.Interop.Excel.Range range;
                   
                range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, 1];
                if (range.Text.ToString().Trim() != "")
                {
                    string _version = range.Text.ToString().Trim();
                    if (_version == version)
                    {
                        res = true;
                    }
                }
                //负责列头End
                return res;
            }
            catch
            {
                return false;
            }
            finally
            {
                IntPtr t = new IntPtr(app.Hwnd);
                int k = 0;
                GetWindowThreadProcessId(t, out  k);
                System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k);
                p.Kill();  
            }

        }

        #endregion
    }
}
